package com.laivi.basic.module.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.laivi.basic.common.constants.Constant;
import com.laivi.basic.model.datastruct.ValueMap;
import com.laivi.basic.model.sql.BasicDB;
import com.laivi.basic.model.sql.BasicDBConn;
import com.laivi.basic.model.syncdata.Database;
import com.laivi.basic.module.log.Log;
import com.laivi.basic.module.log.Logs;
import com.laivi.basic.util.DataUtil;
import com.laivi.basic.util.SqlUtil;

/**
 * @author laivi.zhu@gmail.com
 * @date 2014-7-2
 * description:
 */
public class SqlDao extends ADBDao{
	private final static Log log=Logs.getLog(SqlDao.class);
	
	private Connection conn;
	
	private PreparedStatement pst;
	
	private ResultSet rs;

	public SqlDao(BasicDB db){
		this.db=db;
		this.conn=db.getConn();
	}
	
	public SqlDao(Database db){
		if(DataUtil.isEmpty(db.getUrl())){
			this.db=db.getPool();
		}else{
			this.db=new BasicDBConn(db.getUrl(),db.getUser(),db.getPassword());
		}
	}
	
	public SqlDao(String url,String user,String password){
		this.db=new BasicDBConn(url,user,password);
	}
	
	@Override
	public List<ValueMap> queryParam(String sql,Object... objs) {
		//conn=db.getConn();
		List<ValueMap> values=new ArrayList<ValueMap>();
		try {
			pst=conn.prepareStatement(sql);
			int index=1;
			for(Object obj:objs){
				pst.setObject(index++, obj);
			}
			rs=pst.executeQuery();
			while(rs.next()){
				ValueMap value=ValueMap.createMap();
				for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
					String field=rs.getMetaData().getColumnName(i);
					value.add(field, rs.getObject(field));
				}
				values.add(value);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			close();
		}
		return values;
	}
	
	public void add(String table,ValueMap value){
		conn=db.getConn();
		try {
			pst=SqlUtil.prepareStatement(conn, SqlUtil.mapToInsert(table, value),false);
			pst.executeUpdate();
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			close();
		}
	}
	
	public void add(String table,List<ValueMap> values){
		conn=db.getConn();
		try {
			conn.setAutoCommit(false);
			pst=SqlUtil.prepareStatement(conn, SqlUtil.mapToInsert(table, values),false);
			pst.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			}catch (SQLException e1) {
				e1.printStackTrace();
			} 
		}finally{
			close();
		}
	}
	
	public long insert(String table,ValueMap value){
		conn=db.getConn();
		try {
			pst=SqlUtil.prepareStatement(conn, SqlUtil.mapToInsert(table, value),true);
			pst.executeUpdate();
			rs=pst.getGeneratedKeys();
			if(rs.next()){
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			close();
		}
		return 0;
	}
	
	public void update(String table,List<ValueMap> values,List<Map<String,Object>> where){
		conn=db.getConn();
		try {
			pst=SqlUtil.prepareStatement(conn, SqlUtil.mapToUpdate(table, values,where),false);
			pst.executeUpdate();
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			close();
		}
	}
	
	public void update(String table,List<ValueMap> values,String...whereFields){
		conn=db.getConn();
		try {
			pst=SqlUtil.prepareStatement(conn, SqlUtil.mapToUpdate(table, values,whereFields),false);
			pst.executeUpdate();
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			close();
		}
	}
	
	public void update(String table,ValueMap value,String where){
		conn=db.getConn();
		try {
			pst=SqlUtil.prepareStatement(conn, SqlUtil.mapToUpdate(table, value,where),false);
			pst.executeUpdate();
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			close();
		}
	}
	
	public void execute(String sql){
		//conn=db.getConn();
		try {
			pst=conn.prepareStatement(sql);
			pst.executeUpdate();
		} catch (SQLException e) {
			log.error(e.getMessage());
		}finally{
			//close();
		}
	}
	
	public void close(){
		try {
			if(rs!=null){
				rs.close();
			}
			if(pst!=null){
				pst.close();
			}
			if(conn!=null){
				this.db.close(conn);
				this.conn=null;
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	private List<String> queryTablesOrViews(String type) {
		List<String> tables=new ArrayList<String>();
		conn=db.getConn();
		String user=null;
		switch(this.db.getType()){
		case SQLSERVER:
			user="dbo";
			break;
		case ORACLE:
			user=db.getUser().toUpperCase();
			break;
		default:
			user=Constant.EMPTY;
			break;
		}
		try{
			DatabaseMetaData dm = conn.getMetaData();
			rs=dm.getTables(db.getDataBase(), null, null, new String[]{type});
			while(rs.next()){
				String tableSchem = rs.getString("TABLE_SCHEM");
				if (DataUtil.isEmpty(tableSchem)||user.equalsIgnoreCase(tableSchem)) { 
					tables.add(rs.getString("TABLE_NAME"));
				}
			}
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			close();
		}
		return tables;
	}
	
	public boolean isExist(String table){
		conn=db.getConn();
		boolean flag=false;
		try{
			DatabaseMetaData dm = conn.getMetaData();
			rs=dm.getTables(null, null, table, null);
			if(rs.next()){
				flag= true;
			}
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			close();
		}
		return flag;
	}

	@Override
	public List<ValueMap> queryColunm(String table) {
		List<ValueMap> colunmMap=null;
		conn=db.getConn();
		try{
			switch(this.db.getType()){
			case SQLSERVER:
				colunmMap=this.query("SELECT B.name AS COLUMN_NAME,B.is_nullable as NULLABLE,B.max_length as COLUMN_SIZE," +
						"C.value AS REMARKS,t.name as TYPE_NAME " +
						"FROM sys.tables A INNER JOIN sys.columns B ON B.object_id = A.object_id " +
						"LEFT JOIN sys.types t on B.system_type_id=t.system_type_id " +
						"LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id " +
						"WHERE A.name = '"+table+"'");
				break;
			case ORACLE:
				colunmMap=this.query("select  a.column_name,a.DATA_TYPE as TYPE_NAME,b.comments as REMARKS," +
						"a.CHAR_LENGTH as COLUMN_SIZE,decode(c.column_name,null,'FALSE','TRUE') as ISPRIMARY," +
						"decode(a.NULLABLE,'N','TRUE','Y','FALSE','') as NULLABLE from all_tab_columns a,all_col_comments b," +
						"(select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b " +
						"where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name = '"+table.toUpperCase()+
						"')c " +
						"where a.Table_Name=b.table_Name and a.column_name=b.column_name and a.Table_Name='"+table.toUpperCase()+"' " +
						"and a.owner=b.owner and a.owner='"+db.getUser().toUpperCase()+"' " +
						"and a.COLUMN_NAME = c.column_name(+)  order by a.COLUMN_ID");
				break;
			default:
				colunmMap=new ArrayList<ValueMap>();
				DatabaseMetaData dm = conn.getMetaData();
				rs = dm.getColumns(null, "%", table, "%");
				while(rs.next()){
					ValueMap colunm=ValueMap.createMap()
							.add("COLUMN_NAME", rs.getString("COLUMN_NAME"))//鍒楀悕绉�
							.add("DATA_TYPE", rs.getString("DATA_TYPE"))//鏉ヨ嚜 java.sql.Types 鐨� SQL 绫诲瀷
							.add("TYPE_NAME ", rs.getString("TYPE_NAME"))//鏁版嵁婧愪緷璧栫殑绫诲瀷鍚嶇О锛屽浜� UDT锛岃绫诲瀷鍚嶇О鏄畬鍏ㄩ檺瀹氱殑
							.add("COLUMN_SIZE", rs.getString("COLUMN_SIZE"))//鍒楃殑澶у皬
							.add("DECIMAL_DIGITS", rs.getString("DECIMAL_DIGITS"))//灏忔暟閮ㄥ垎鐨勪綅鏁般�傚浜� DECIMAL_DIGITS 涓嶉�傜敤鐨勬暟鎹被鍨嬶紝鍒欒繑鍥� Null
							.add("NULLABLE", rs.getString("NULLABLE"))//鏄惁鍏佽浣跨敤 NULL;columnNoNulls - 鍙兘涓嶅厑璁镐娇鐢∟ULL鍊�,columnNullable - 鏄庣‘鍏佽浣跨敤NULL鍊�,columnNullableUnknown - 涓嶇煡閬撴槸鍚﹀彲浣跨敤 null
							.add("REMARKS", rs.getString("REMARKS"))//鍒楃殑娉ㄩ噴
							.add("COLUMN_DEF", rs.getString("COLUMN_DEF"))//榛樿鍊�
							.add("IS_AUTOINCREMENT", rs.getString("IS_AUTOINCREMENT"));//姝ゅ垪鏄惁鑷姩澧炲姞
					colunmMap.add(colunm);
				}
				break;
			}
			
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			close();
		}
		return colunmMap;
	}

	@Override
	public List<String> queryViews() {
		return queryTablesOrViews("VIEW");
	}

	@Override
	public List<String> queryTables() {
		return queryTablesOrViews("TABLE");
	}

}
